﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for ClassDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class LOP_HOC_DAO
    {
        private const string SQL_SELECT_ALL = "select * from LOP_HOC where TEN_LOP_HOC like @TEN_LOP_HOC";
        private const string SQL_SELECT_CURRENT_LOPHOC = "select * from  LOP_HOC where TRANG_THAI=0";

        private const string SQL_UPDATE_CLASS = "update LOP_HOC set TEN_LOP_HOC = @TEN_LOP_HOC, NGAY_BD = @NGAY_BD, NGAY_KT = @NGAY_KT, MA_LOAI_LOP = @MA_LOAI_LOP, CA_HOC = @CA_HOC where MA_LOP_HOC = @MA_LOP_HOC";
        private const string SQL_INSERT_CLASS = "insert into LOP_HOC (TEN_LOP_HOC, NGAY_BD, NGAY_KT, MA_LOAI_LOP, CA_HOC) "
            + "values (@TEN_LOP_HOC, @NGAY_BD, @NGAY_KT, @MA_LOAI_LOP, @CA_HOC)";
        private const string SQL_DELETE_CLASS = "delete from LOP_HOC where MA_LOP_HOC = @MA_LOP_HOC";
        private const string SQL_SELECT_CLASS_TYPE = "select TEN_DOI_TUONG, MA_DOI_TUONG from DOI_TUONG where LOP_HOC like 'LOAI_LOP'";
        private const string SQL_END_CLASS = "update LOP_HOC set NGAY_KT = @NGAY_KT , TRANG_THAI = @TRANG_THAI where MA_LOP_HOC = @MA_LOP_HOC";

        // Parameters
        private const string PARM_CLASS_ID = "@MA_LOP_HOC";
        private const string PARM_CLASS_NAME = "@TEN_LOP_HOC";
        private const string PARM_CLASS_START = "@NGAY_BD";
        private const string PARM_CLASS_END = "@NGAY_KT";
        private const string PARM_CLASS_CA_HOC = "@CA_HOC";
        private const string PARM_CLASS_TYPE_ID = "@MA_LOAI_LOP";
        private const string PARM_CLASS_IS_ENDED = "@TRANG_THAI";

        #region Public Methods
        public IList<LOP_HOC> GetAll(string tenLop)
        {
            IList<LOP_HOC> LOPHOC = new List<LOP_HOC>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_CLASS_NAME, SqlDbType.NVarChar)};
            parms[0].Value = tenLop;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ALL, parms))
            {
                while (reader.Read())
                {
                    LOP_HOC cs = new LOP_HOC();
                    cs.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));
                    cs.TEN_LOP_HOC = reader.GetString(reader.GetOrdinal("TEN_LOP_HOC"));
                    cs.NGAY_BD = reader.GetDateTime(reader.GetOrdinal("NGAY_BD"));
                    cs.NGAY_KT = reader.GetDateTime(reader.GetOrdinal("NGAY_KT"));
                    cs.MA_LOAI_LOP = new DOI_TUONG();
                    cs.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_LOAI_LOP"));
                    cs.CA_HOC = reader.GetString(reader.GetOrdinal("CA_HOC"));
                    cs.TRANG_THAI = reader.GetBoolean(reader.GetOrdinal("TRANG_THAI"));
                    LOPHOC.Add(cs);
                }
            }
            return LOPHOC;
        }

        public IList<LOP_HOC> GetCurrentLOPHOC()
        {
            IList<LOP_HOC> LOPHOC = new List<LOP_HOC>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_CURRENT_LOPHOC, null))
            {
                while (reader.Read())
                {
                    LOP_HOC cs = new LOP_HOC();
                    cs.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));
                    cs.TEN_LOP_HOC = reader.GetString(reader.GetOrdinal("TEN_LOP_HOC"));
                    cs.NGAY_BD = reader.GetDateTime(reader.GetOrdinal("NGAY_BD"));
                    cs.NGAY_KT = reader.GetDateTime(reader.GetOrdinal("NGAY_KT"));
                    
                    cs.MA_LOAI_LOP = new DOI_TUONG();
                    cs.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_LOAI_LOP"));
                    cs.CA_HOC = reader.GetString(reader.GetOrdinal("CA_HOC"));
                    cs.TRANG_THAI = reader.GetBoolean(reader.GetOrdinal("TRANG_THAI"));
                    LOPHOC.Add(cs);
                }
            }
            return LOPHOC;
        }

        public IList<QuanLyHocPhi.Entity.DOI_TUONG> GetClassType()
        {
            IList<QuanLyHocPhi.Entity.DOI_TUONG> objects = new List<QuanLyHocPhi.Entity.DOI_TUONG>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_CLASS_TYPE, null))
            {
                while (reader.Read())
                {
                    QuanLyHocPhi.Entity.DOI_TUONG obj = new QuanLyHocPhi.Entity.DOI_TUONG();
                    obj.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));
                    obj.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    objects.Add(obj);
                }
            }
            return objects;
        }

        public Boolean Insert(LOP_HOC cs)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_CLASS_NAME, SqlDbType.NVarChar),
											 new SqlParameter(PARM_CLASS_START, SqlDbType.DateTime),
                                             new SqlParameter(PARM_CLASS_END, SqlDbType.DateTime),
                                             new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_CLASS_CA_HOC, SqlDbType.NVarChar)};
                parms[0].Value = cs.TEN_LOP_HOC;
                parms[1].Value = cs.NGAY_BD;
                parms[2].Value = cs.NGAY_KT;
                parms[3].Value = cs.MA_LOAI_LOP.MA_DOI_TUONG;
                parms[4].Value = cs.CA_HOC;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT_CLASS, parms);
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(LOP_HOC cs)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_CLASS_NAME, SqlDbType.NVarChar),
											 new SqlParameter(PARM_CLASS_START, SqlDbType.DateTime),
                                             new SqlParameter(PARM_CLASS_END, SqlDbType.DateTime),
                                             new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_CLASS_CA_HOC, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_CLASS_ID, SqlDbType.Int)};
            parms[0].Value = cs.TEN_LOP_HOC;
            parms[1].Value = cs.NGAY_BD;
            parms[2].Value = cs.NGAY_KT;
            parms[3].Value = cs.MA_LOAI_LOP.MA_DOI_TUONG;
            parms[4].Value = cs.CA_HOC;
            parms[5].Value = cs.MA_LOP_HOC;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE_CLASS, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int MA_LOP_HOC)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_CLASS_ID, SqlDbType.Int) };
            parms[0].Value = MA_LOP_HOC;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE_CLASS, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool EndClass(LOP_HOC cs)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                             new SqlParameter(PARM_CLASS_END, SqlDbType.DateTime),
                                             new SqlParameter(PARM_CLASS_IS_ENDED, SqlDbType.Bit),
                                             new SqlParameter(PARM_CLASS_ID, SqlDbType.Int)};
            parms[0].Value = cs.NGAY_KT;
            parms[1].Value = cs.TRANG_THAI;
            parms[2].Value = cs.MA_LOP_HOC;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_END_CLASS, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        #endregion
    }
}